﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MJM.RPP.EntidadesNegocio;
using MJM.CBD.Conn;

namespace MJM.RPP.AccesoDatos
{
    public static class ComercioAD
    {
        public static int Insertar(ComercioEN obj)
        {
            int codigo = 1;
            SqlConnection conn = new SqlConnection(Cadena.CadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_i_T_MAES_EMPCOM_Insertar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chRUC", obj.RUC));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chNOMEMP", obj.Nombre));
                cmd.Parameters.Add(new SqlParameter("@vRUBEMP_P_inID", obj.IdRubro));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chNOMCON", obj.Contacto));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chNROCON", obj.NroContacto));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chDICCOM", obj.Direccion));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chDISCOM", obj.Distrito));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chTELEMP", obj.Telefono));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chWEBEMP", obj.Web));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chNUMLIC", obj.Licencia));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chNUMEXP", obj.Expediente));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chANOEXP", obj.AnioExpediente));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chANOLIC", obj.AnioLicencia));
                cmd.Parameters.Add(new SqlParameter("@vDIRE_P_inID", obj.CodigoDireccion));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_byLOGEMP", obj.LogoComercio));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chNOMLOG", obj.NombreArchivo));
                SqlParameter prm = new SqlParameter();
                prm.Direction = ParameterDirection.ReturnValue;
                prm.SqlDbType = SqlDbType.Int;
                cmd.Parameters.Add(prm);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                codigo = Convert.ToInt32(prm.Value);
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
                codigo = 0;
            }
            return codigo;
        }

        public static List<ComercioEN> ListaComercios(ComercioEN obj)
        {
            SqlConnection conn = new SqlConnection(Cadena.CadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_s_T_MAES_EMPCOM_Listar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            List<ComercioEN> lst = new List<ComercioEN>();
            SqlDataReader dr;
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chNOMEMP", obj.Nombre));
                cmd.Parameters.Add(new SqlParameter("@vRUBEMP_P_inID", obj.IdRubro));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_inFLAELI", obj.Eliminado));
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_chRUC", obj.RUC));

                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    var objRecor = new ComercioEN();
                    objRecor.Codigo = dr.GetInt32(dr.GetOrdinal("EMPCOM_P_inID"));
                    objRecor.RUC = dr.GetString(dr.GetOrdinal("EMPCOM_chRUC"));
                    objRecor.Nombre = dr.GetString(dr.GetOrdinal("EMPCOM_chNOMEMP"));
                    objRecor.IdRubro = dr.GetInt32(dr.GetOrdinal("RUBEMP_P_inID"));
                    objRecor.Contacto = dr.GetString(dr.GetOrdinal("EMPCOM_chNOMCON"));
                    objRecor.NroContacto = dr.GetString(dr.GetOrdinal("EMPCOM_chNROCON"));
                    objRecor.Direccion = dr.GetString(dr.GetOrdinal("EMPCOM_chDICCOM"));
                    objRecor.Distrito = dr.GetString(dr.GetOrdinal("EMPCOM_chDISCOM"));
                    objRecor.Telefono = dr.GetString(dr.GetOrdinal("EMPCOM_chTELEMP"));
                    objRecor.Web = dr.GetString(dr.GetOrdinal("EMPCOM_chWEBEMP"));
                    objRecor.Licencia = dr.GetString(dr.GetOrdinal("EMPCOM_chNUMLIC"));
                    objRecor.Expediente = dr.GetString(dr.GetOrdinal("EMPCOM_chNUMEXP"));
                    objRecor.AnioExpediente = dr.GetString(dr.GetOrdinal("EMPCOM_chANOEXP"));
                    objRecor.AnioLicencia= dr.GetString(dr.GetOrdinal("EMPCOM_chANOLIC"));
                    objRecor.CodigoDireccion = dr.GetInt32(dr.GetOrdinal("DIRE_P_inID"));
                    objRecor.LogoComercio = (byte[])dr[dr.GetOrdinal("EMPCOM_byLOGEMP")];
                    objRecor.NombreArchivo = dr.GetString(dr.GetOrdinal("EMPCOM_chNOMLOG"));
                    objRecor.Rubro = dr.GetString(dr.GetOrdinal("RUBCOM_chDESRUB"));
                    objRecor.Eliminado = dr.GetInt32(dr.GetOrdinal("EMPCOM_inFLAELI"));
                    lst.Add(objRecor);
                }
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return lst;
        }

        public static int Eliminar(int IdComercio)
        {
            SqlConnection conn = new SqlConnection(Cadena.CadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_u_T_MAES_EMPCOM_Eliminar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            int Codigo=1;
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vEMPCOM_P_inID", IdComercio));
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return Codigo;
        }

        public static List<string> ListarViasObservatorio()
        {
            List<string> Vias=new List<string>();
            SqlConnection conn = new SqlConnection(Cadena.CadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_s_T_DIRE_DESC_Listar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr;
            try
            {
                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Vias.Add(dr.GetString(0));
                }
                cmd.Connection.Close();
                cmd.Dispose();
                conn.Dispose();
            }
            catch(Exception ex)
            {
                cmd.Dispose();
                conn.Dispose();
            }
            return Vias;
        }

        public static List<AreaEN> ListarRubros()
        {
            List<AreaEN> lst = new List<AreaEN>();
            SqlConnection conn = new SqlConnection(Cadena.CadenaPadron());
            SqlCommand cmd = new SqlCommand("sp_s_T_MAES_RUBCOM_Listar", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr;
            try
            {
                cmd.Connection.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    AreaEN obj = new AreaEN();
                    obj.Codigo = dr.GetInt32(0);
                    obj.Area = dr.GetString(1);
                    lst.Add(obj);
                }
                cmd.Connection.Close();
                conn.Dispose();
                cmd.Dispose();
            }
            catch(Exception ex)
            {
                conn.Dispose();
                cmd.Dispose();
            }
            return lst;
        }
    }
}